How to Unwind a Dimension in TM1

The Problem: The Need to Unwind

Often when writing dimension update processes from a datasource which is updated automatically we use a standard procedure. Unfortunately these steps sometimes don’t do what we need and we must unwind the dimension in the Dim Build process. Our normal steps are:

1. Delete all the Elements
2. Add Top Level Consolidations
3. Cycle through (Metadata) Adding Elements and Element Components
4. Cycle through (Data) Adding Aliases

But in some cases we are only given a partial data feed.  For example we may be reporting off 3 years sales but the datasource for the Product dimension is broken into a file per year. If we were to use the DimensionDeleteAllElements() function within the first process (which imports the first years products), the additional products from subsequent years will not exist until the next process is executed. The data associated with the subsequent year’s products will be lost when those products aren’t added into the Dimension immediately as part of the same Process/ Chore Execution.

Note: You can still use DimensionDeleteAllElements in the first of three processes without losing data if the processes are always executed together in a Chore – but this is reeks of potential for user error.

In the same example, if we were to skip the DimensionDeleteAllElements() and an element moved from one consolidation to another (from a change to the current years file). As there is no cleanup part to the overall process, the product will reside under both consolidations producing garbage data.

The Solution

The solution is to break down the structures/hierarchies within the dimension without deleting any elements. This is done instead of calling the DimensionDeleteAllElements() function. To do this the TI Script needs to loop through each element in the dimension and remove each child. While explained simply enough in the code, Loops in Turbo Integrator are avoided by most people. So here is a standard script you can use – plug and play.

It is created using the WHILE looping function, then DIMSIZ, DIMNM, ELCOMPN, ELCOMP and DIMENSIONELEMENTCOMPONENTDELETE

All you need to do is define the variable “DimName” and then add in the code to unwind.

DimName = 'Product';

#Unwind the dimension we are updating
iElm = 1;
ElmCount = DIMSIZ(DimName);
WHILE(iElm < ElmCount);
    sElm = DIMNM(DimName,iElm);
    ChildCount = ELCOMPN(DimName,sElm);
    WHILE(ChildCount > 0);
        sChildElm = ELCOMP(DimName,sElm,ChildCount);
        DIMENSIONELEMENTCOMPONENTDELETE(DimName, sElm, sChildElm );
        ChildCount = ChildCount - 1;
    END;
    iElm = iElm + 1;
END;

The Bedrock Equivalent

Bedrock provides a simple solution here in a single line.

EXECUTEPROCESS('Bedrock.Dim.Hierarchy.Unwind.All',
                                'pDimension','Product','pDebug',0);

Obviously you need to have the Bedrock process that this calls if you want to execute it, whereas the code in our example above can just be inserted directly into the TI.